package cn.edu.zime.bigdata.dao.mapper;

import cn.edu.zime.bigdata.dao.model.*;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface StudentMapper {

    @Insert("insert into students(id, name, genda, level, depart, ability, grade, dengji) values(#{id},#{name},#{genda},#{level},#{depart},#{ability},#{grade},#{dengji})")
    public int insertStudent(Student u);

    @Select("select * from students")
    public List<Student> selectAllStudents();

    @Select("select * from students where id like CONCAT('%', #{id}, '%') or name like CONCAT('%', #{name}, '%')")
    public List<Student> selectStudents(String id, String name);

    @Update("update students set name=#{name}, genda=#{genda}, level=#{level}, depart=#{depart}, ability=#{ability}, grade=#{grade}, dengji=#{dengji} where id=#{id}")
    public int updateStudent(Student student);

    @Delete("delete from students where id = #{id}")
    public int deleteStudent(String id);

    @Select("select * from students where grade<60")
    public List<CountValue> selectGendaRatio();

    @Select("SELECT  CONCAT(COUNT(*)) as value  , dengji as week FROM students  GROUP BY dengji")
    public List<StudentGradeStat> selectupStudents();

    @Select("SELECT  COUNT(*) as value , depart as name FROM students WHERE grade < 60 GROUP BY depart")
    public List<Studentss> selectddStudents();
}